package com.itheima;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;


public class POItest {

    @Test//从本地文件的excel中读取内容打印到控制台
    public void test1() throws Exception {
        //读取指定excel文件中的信息
        String filePath = "F:\\资料\\传智健康代码\\05\\传智健康-Day05\\我的报表.xls";
        Workbook workbook = null;
        if (filePath.endsWith("xls")) workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
        else if (filePath.endsWith("xlsx")) workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
        else throw new RuntimeException("文件格式不对");

        //获取工作薄中的第一张表
        Sheet sheet = workbook.getSheetAt(0);
        //获取表中的最后一行,行号是从0开始计算的，lastRowNum为最后一行的行号数字-1，所以在遍历所有行的时候使用=
        int lastRowNum = sheet.getLastRowNum();
        //循环获取所有的行
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            //获取所有的单元格，定义一个字符串用来拼接每一行的打印信息
            String message = "";
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        message += "" + "\t";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        message += cell.getBooleanCellValue() + "\t";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        message += cell.getNumericCellValue() + "\t";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        message += cell.getErrorCellValue() + "\t";
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        message += cell.getNumericCellValue() + "\t";
                        break;
                    case Cell.CELL_TYPE_STRING:
                        message += cell.getStringCellValue() + "\t";
                        break;
                }
            }
            System.out.println(message);
        }
        workbook.close();
    }

    @Test//通过java创建excel表保存在本地中
    public void test2() throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("heima");
        Row title = sheet.createRow(0);
        //给第一行添加信息，为行中的单元格赋值
        title.createCell(0).setCellValue("姓名");
        title.createCell(1).setCellValue("性别");
        title.createCell(2).setCellValue("年龄");

        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("小明");
        row1.createCell(1).setCellValue("男");
        row1.createCell(2).setCellValue(18);

        Row row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue("小红");
        row2.createCell(1).setCellValue("女");
        row2.createCell(2).setCellValue(20);

        //创建输出流把生成的表格输出到本地
        FileOutputStream fos = new FileOutputStream("F:\\资料\\传智健康代码\\05\\传智健康-Day05\\我的报表1.xlsx");
        workbook.write(fos);
        workbook.close();
    }
}
